package DBLayer;
import ModelLayer.Supplier;
import java.sql.*;
import java.util.ArrayList;

public class DBSupplier {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBSupplier() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Supplier getSupplierByID(int id) {
        return selectSingle("SELECT `id_supplier`, `name`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `description`, `meta_title`, `meta_keywords`, `meta_description` FROM `ps_supplier` LEFT JOIN `ps_supplier_lang` USING (`id_supplier`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `id_supplier` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Supplier> getSupplierList() {
        return selectMultiple("SELECT `id_supplier`, `name`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `description`, `meta_title`, `meta_keywords`, `meta_description` FROM `ps_supplier` LEFT JOIN `ps_supplier_lang` USING (`id_supplier`) WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL ORDER BY `id_supplier` ASC;");
    }

    private Supplier selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Supplier supplier = new Supplier();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                supplier = buildSupplier(result);
            }
            System.out.println("Fetching Supplier Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return supplier;
    }

    private ArrayList<Supplier> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Supplier> suppliers = new ArrayList<Supplier>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Supplier supplier = new Supplier();
                supplier = buildSupplier(result);
                suppliers.add(supplier);
            }
            System.out.println("Fetching Supplier List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return suppliers;
    }

    private Supplier buildSupplier(ResultSet result) {
        Supplier supplier = new Supplier();
        try {
            supplier.setIDSupplier(result.getInt("id_supplier"));
            supplier.setName(result.getString("name"));
            supplier.setDateAdd(result.getInt("date_add"));
            supplier.setDateUpd(result.getInt("date_upd"));
            supplier.setDescription(result.getString("description"));
            supplier.setMetaTitle(result.getString("meta_title"));
            supplier.setMetaKeywords(result.getString("meta_keywords"));
            supplier.setMetaDescription(result.getString("meta_description"));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return supplier;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addSupplier(Supplier supplier) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_supplier_lang` (`id_supplier`, `description`, `meta_title`, `meta_keywords`, `meta_description`) "
                + "VALUES (" + supplier.getIDSupplier() + ", '" + supplier.getDescription() + "', '" + supplier.getMetaTitle() + "', '" + supplier.getMetaKeywords() + "', '" + supplier.getMetaDescription() + "');";

        query += "INSERT INTO `ps_supplier` (`id_supplier`, `name`, `date_add`, `date_upd`) "
                + "VALUES (" + supplier.getIDSupplier() + ", '" + supplier.getName() + "', FROM_UNIXTIME(" + supplier.getDateAdd() + "), FROM_UNIXTIME(" + supplier.getDateUpd() + "));";
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Supplier ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateSupplier(Supplier supplier, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_supplier` SET "
                + "`id_supplier`            = "  + supplier.getIDSupplier() + ", "
                + "`name`                   = '" + supplier.getName() + "', "
                + "`date_add`               = FROM_UNIXTIME("  + supplier.getDateAdd() + "), "
                + "`date_upd`               = FROM_UNIXTIME("  + supplier.getDateUpd() + ") "
                + "WHERE `id_supplier` = " + id + " LIMIT 1; ";

        query += "UPDATE `ps_supplier_lang` SET "
                + "`id_supplier`            = "  + supplier.getIDSupplier() + ", "
                + "`description`            = '" + supplier.getDescription() + "', "
                + "`meta_title`             = '" + supplier.getMetaTitle() + "', "
                + "`meta_keywords`          = '" + supplier.getMetaKeywords() + "', "
                + "`meta_description`       = '" + supplier.getMetaDescription() + "' "
                + "WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_supplier` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = supplier.getIDSupplier();
            System.out.println("Updated Supplier ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteSupplier(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_supplier` WHERE `id_supplier` = " + id + " LIMIT 1; DELETE FROM `ps_supplier_lang` WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_supplier` = " + id + " LIMIT 1;");
            System.out.println("Deleted Product ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
